﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Web;
using System.Windows.Forms;
using Microsoft.Office;
using BUS;
using DTO;
namespace QL_KhachSan
{
    public partial class frmXemDoanhThu : Form
    {
        DataTable dtChiTiet = null;
        int tongDoanhThu = 0;
        public frmXemDoanhThu()
        {
            InitializeComponent();
        }
        public void HienThiChiTietDoanhThu(DataTable dt)
        {

            dtgvChiTietDoanhThu.Rows.Clear();            
            int stt = 1;
            foreach (DataRow dr in dt.Rows)
            {
                string ngay = dr["NgayLap"].ToString();
                string tongTien = dr["ThanhTien"].ToString();
                string khachhang = dr["TenKH"].ToString();
                //
                String[] row = new String[] { stt++.ToString(),ngay,tongTien,khachhang};
                dtgvChiTietDoanhThu.Rows.Add(row);
            }
        }
        private void btn_XemBaoCao_Click(object sender, EventArgs e)
        {
            int nam,thang;
            if (txtNam.Text != "")
            {
                if (cbxThang.Text != "Chọn Tháng")
                {
                    thang = int.Parse(cbxThang.Text.ToString());
                    nam = int.Parse(txtNam.Text);
                    //
                    DataTable dt = XuLyHoaDonBUS.layDanhSachHoaDon();
                    dtChiTiet = XuLyHoaDonBUS.layDanhSachHoaDonTheoThang(thang, nam);
                    if (dt.Rows.Count > 0)
                    {
                        foreach (DataRow dr in dt.Rows)
                        {
                            DateTime ngayLap = DateTime.Parse(dr["NgayLap"].ToString());
                            if (ngayLap.Year == int.Parse(txtNam.Text) && ngayLap.Month == int.Parse(cbxThang.Text.ToString()))
                            {
                                tongDoanhThu += int.Parse(dr["ThanhTien"].ToString());
                            }
                            lblTongDoanhThu.Text = tongDoanhThu.ToString();
                        }
                        HienThiChiTietDoanhThu(dtChiTiet);
                    }
                }
                else
                {
                    MessageBox.Show("Hãy chọn một tháng", "Thông Báo");
                } 
            }
            else
            {
                MessageBox.Show("Nhập vào năm","Thông Báo");
            }            
        }

        private void frmXemDoanhThu_Load(object sender, EventArgs e)
        {
            cbxThang.Text = "Chọn Tháng";
            for (int i = 1; i <= 12; i++)
            {
                cbxThang.Items.Add(i);
                cbxThang.ValueMember = i.ToString();
            }                        
        }

        private void btnXuatFile_Click(object sender, EventArgs e)
        {
            if (dtChiTiet != null)
                ExportToExcel(dtChiTiet);
            else
                MessageBox.Show("Tháng bạn chọn không có khách hàng","Thông Báo");
        }
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
        public void ExportToExcel(DataTable dt)
        {            
            Microsoft.Office.Interop.Excel.Application xlApp ;
            Microsoft.Office.Interop.Excel.Workbook xlWorkBook ;
            Microsoft.Office.Interop.Excel.Worksheet xlWorkSheet ;
            object misValue = System.Reflection.Missing.Value;
            Microsoft.Office.Interop.Excel.Range chartRange;

            xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
            xlWorkBook = xlApp.Workbooks.Add(misValue);

            xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Worksheets.get_Item(1);
            //add data                        
            xlWorkSheet.get_Range("b2", "e3").Merge(false);

            chartRange = xlWorkSheet.get_Range("b2", "e3");
            chartRange.FormulaR1C1 = "BÁO CÁO DOANH THU";
            chartRange.HorizontalAlignment = 3;
            chartRange.VerticalAlignment = 3;

            chartRange = xlWorkSheet.get_Range("b4", "e4");
            chartRange.Font.Bold = true;
            chartRange = xlWorkSheet.get_Range("b9", "e9");
            chartRange.Font.Bold = true;

            chartRange = xlWorkSheet.get_Range("b2", "e9");
            chartRange.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlMedium, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic);
            xlWorkSheet.Cells[4, 2] = "Số thứ tự";
            xlWorkSheet.Cells[4, 3] = "Ngày Tháng";
            xlWorkSheet.Cells[4, 4] = "Tổng tiền";
            xlWorkSheet.Cells[4, 5] = "Tên Khách Hàng";

            //
            int row = dt.Rows.Count;
            //
            chartRange = xlWorkSheet.get_Range("c9", "c"+row);
            chartRange.EntireColumn.ColumnWidth = 30;
            //
            chartRange = xlWorkSheet.get_Range("d9", "d" + row);
            chartRange.EntireColumn.ColumnWidth = 30;
            //
            chartRange = xlWorkSheet.get_Range("e9", "e" + row);
            chartRange.EntireColumn.ColumnWidth = 30;
            //
                int i = 5;
                foreach (DataRow dr in dt.Rows)
                {                    

                    xlWorkSheet.Cells[i, 2] = i-4;
                    xlWorkSheet.Cells[i, 3] = dr["NgayLap"].ToString();
                    xlWorkSheet.Cells[i, 4] = dr["ThanhTien"].ToString();
                    xlWorkSheet.Cells[i, 5] = dr["TenKH"].ToString();
                    i++;
                }                
                //  
                xlWorkSheet.Cells[i, 4] = "Tổng Doanh Thu";
                xlWorkSheet.Cells[i, 5] = tongDoanhThu.ToString();
                //
                chartRange = xlWorkSheet.get_Range("d"+i.ToString(), "e"+i.ToString());
                chartRange.Font.Bold = true;                
            //end
            xlWorkBook.SaveAs("D:\\csharp-Excel.xls", Microsoft.Office.Interop.Excel.XlFileFormat.xlWorkbookNormal, misValue, misValue, misValue, misValue, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive, misValue, misValue, misValue, misValue, misValue);
            xlWorkBook.Close(true, misValue, misValue);
            xlApp.Quit();

            releaseObject(xlWorkSheet);
            releaseObject(xlWorkBook);
            releaseObject(xlApp);

            MessageBox.Show("Excel file created , you can find the file D:\\csharp-Excel.xls");        
        }
    }
}
